Course: Microsoft Power BI Training
Topic: Using SUMX, AVERAGEX, and Other Iterator Functions
Course Link: Microsoft Power BI Training at JustAcademy
Demo Link: Register for a Free Course Demo
JustAcademy Website: Visit JustAcademy
Data Analysis Expressions (DAX) is the formula language used in Power BI to create measures, calculated columns, and calculated tables. Iterator functions are an important part of DAX because they evaluate an expression separately for each row in a table and then combine the individual results.
Functions such as SUMX, AVERAGEX, MINX, MAXX, COUNTX, and RANKX are useful when a calculation requires more than simply aggregating the values of one column.
For example, imagine a sales table containing quantity and unit price. If the table does not already contain a total sales column, you can calculate sales for every row by multiplying quantity by unit price, then add those row-level results together. The SUMX function performs this type of calculation.
An iterator is a function that processes a table one row at a time. For each row, it evaluates an expression using that row's values. Depending on the function, it then sums, averages, counts, finds a minimum or maximum, or otherwise processes the results.
Conceptually, an iterator follows this process:
DAX provides both simple aggregation functions and iterator functions. The key difference is what each function evaluates.
| Function | Type | Purpose | Example |
|---|---|---|---|
SUM |
Aggregation | Adds values from one column. | SUM(Sales[Amount]) |
SUMX |
Iterator | Evaluates an expression for every row, then sums the results. | SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) |
AVERAGE |
Aggregation | Calculates the arithmetic mean of a numeric column. | AVERAGE(Sales[Amount]) |
AVERAGEX |
Iterator | Evaluates an expression for each row, then averages the numeric results. | AVERAGEX(Sales, Sales[Quantity] * Sales[UnitPrice]) |
MIN |
Aggregation | Returns the smallest value in a column. | MIN(Sales[Amount]) |
MINX |
Iterator | Finds the smallest result of a row-by-row expression. | MINX(Sales, Sales[Quantity] * Sales[UnitPrice]) |
MAX |
Aggregation | Returns the largest value in a column. | MAX(Sales[Amount]) |
MAXX |
Iterator | Finds the largest result of a row-by-row expression. | MAXX(Sales, Sales[Quantity] * Sales[UnitPrice]) |
Suppose the Sales table contains these rows:
| Product | Quantity | Unit Price | Quantity × Unit Price |
|---|---|---|---|
| Laptop | 2 | 500 | 1,000 |
| Monitor | 3 | 200 | 600 |
| Keyboard | 5 | 40 | 200 |
| Total | 10 | — | 1,800 |
If the table contains an existing Sales[Amount] column, a measure can sum it directly:
Total Sales = SUM(Sales[Amount])If the amount must be calculated from quantity and unit price, use SUMX:
Total Sales = SUMX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)The expression is evaluated for each row, producing 1,000, 600, and 200. These results are then added to return 1,800.
To use iterator functions correctly, it is important to understand two DAX concepts: row context and filter context.
Row context means DAX is evaluating an expression for a particular row. Iterator functions such as SUMX and AVERAGEX create row context for the rows they iterate over.
For example:
Revenue = SUMX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)While processing a row, DAX reads the quantity and unit price from that row, multiplies them, and moves on to the next row.
Filter context is the set of filters that determines which data is included in a calculation. It can come from report slicers, visual rows and columns, page filters, report filters, relationships, and DAX expressions.
For example, if a report is filtered to the year 2026, a sales measure normally evaluates only the sales rows visible under that filter context, assuming the model's relationships and filter directions support the filter.
An iterator evaluates its table expression under the current filter context and then evaluates its second argument for each row in the resulting table. Consequently, a measure using SUMX can return different results for different products, regions, dates, or customer segments in a report.
Important: An iterator does not automatically remove report filters. It works with the table that is available under the current context unless the DAX expression explicitly changes that context.
SUMX is an iterator function that evaluates an expression for every row of a table and returns the sum of the numeric results.
SUMX(<table>, <expression>)Assume the Sales table includes Quantity and UnitPrice columns.
Total Revenue =
SUMX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)This measure multiplies the quantity by the unit price for each sales row and sums the results. It is useful when revenue has not been stored as a separate column or when the calculation needs to be defined dynamically.
Suppose each sales row includes a discount rate stored as a decimal, such as 0.10 for 10%.
Net Revenue =
SUMX(
Sales,
Sales[Quantity] *
Sales[UnitPrice] *
(1 - Sales[DiscountRate])
)If quantity is 2, unit price is 100, and the discount rate is 0.10, the row's net revenue is 180. The measure calculates this value for each row and adds the results.
If the table includes quantity, selling price, and unit cost, profit can be calculated for every row.
Total Profit =
SUMX(
Sales,
Sales[Quantity] *
(Sales[UnitPrice] - Sales[UnitCost])
)This measure calculates the total profit based on the difference between unit selling price and unit cost, multiplied by quantity.
Sometimes a calculation should include only selected rows, such as sales above a certain amount. You can combine SUMX with FILTER.
Revenue from High-Value Sales =
SUMX(
FILTER(
Sales,
Sales[Quantity] * Sales[UnitPrice] > 1000
),
Sales[Quantity] * Sales[UnitPrice]
)This example includes only rows whose calculated revenue exceeds 1,000, then sums the revenue of those rows. The threshold is evaluated per row, not against the final grand total.
AVERAGEX evaluates an expression for each row in a table and calculates the arithmetic mean of the numeric results.
AVERAGEX(<table>, <expression>)Average Transaction Value =
AVERAGEX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)If the three row values are 1,000, 600, and 200, the average is (1,000 + 600 + 200) / 3 = 600. This example averages the calculated value for each row in the iterated Sales table. If each row represents an order line rather than an entire order, the result is the average order-line value, not necessarily the average order value.
Average Profit per Sales Row =
AVERAGEX(
Sales,
Sales[Quantity] *
(Sales[UnitPrice] - Sales[UnitCost])
)This measure calculates profit for each sales row and averages those row-level profit values. It is not the same as total profit divided by the number of distinct orders unless the table grain and calculation make those quantities equivalent.
When the goal is to calculate average revenue per product, iterate over a table containing one row per product rather than over individual sales lines.
Average Revenue per Product =
AVERAGEX(
VALUES(Products[ProductID]),
[Total Revenue]
)VALUES(Products[ProductID]) returns the distinct product IDs visible in the current filter context. The measure evaluates [Total Revenue] for each product and averages the resulting product-level values.
This can differ substantially from AVERAGEX(Sales, ...), because a product can have many sales rows. The table supplied to the iterator defines what counts as one observation.
MINX and MAXX evaluate an expression for each row and return the smallest or largest numeric result, respectively.
MINX(<table>, <expression>)Lowest Line Revenue =
MINX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)This measure finds the smallest calculated revenue among the rows in the iterated Sales table that are visible under the current filter context.
MAXX(<table>, <expression>)Highest Line Revenue =
MAXX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)This measure returns the largest calculated line revenue. It can be used to identify the largest transaction line, highest calculated cost, or maximum row-level profit.
Count iterators can count results of expressions evaluated row by row. The choice between them depends on what should count as a value.
COUNTX evaluates an expression over a table and counts the nonblank results that are numeric or otherwise countable under its documented type rules. It is often used when the expression returns a number.
Count Positive Revenue Rows =
COUNTX(
FILTER(
Sales,
Sales[Quantity] * Sales[UnitPrice] > 0
),
Sales[Quantity] * Sales[UnitPrice]
)This measure counts the qualifying rows with a nonblank numeric revenue expression. Because the filter condition requires revenue above zero, it counts positive-revenue rows in the current context.
COUNTAX evaluates an expression and counts nonblank results, including text results. It can be useful when the expression returns text rather than a number.
Count Rows with Product Name =
COUNTAX(
Sales,
RELATED(Products[ProductName])
)This example counts sales rows for which the related product name expression returns a nonblank value. It assumes an appropriate relationship exists between the Sales and Products tables.
| Function | Primary purpose |
|---|---|
COUNT |
Counts nonblank values in a column, subject to its supported data types. |
COUNTA |
Counts nonblank values in a column, including supported nonnumeric values. |
COUNTX |
Evaluates an expression per row and counts eligible nonblank results, commonly numeric results. |
COUNTAX |
Evaluates an expression per row and counts nonblank results, including text. |
COUNTROWS |
Counts the rows in a table expression. |
Use COUNTROWS when the requirement is to count rows, regardless of whether a particular expression is blank. Use COUNTX or COUNTAX when the count depends on the result of an expression.
PRODUCTX evaluates a numeric expression for every row and returns the product of the resulting values. It is useful for mathematical calculations in which values need to be multiplied across rows.
PRODUCTX(<table>, <expression>)If a table contains one row per period and a numeric growth rate, a growth factor can be calculated as 1 plus the growth rate.
Combined Growth Factor =
PRODUCTX(
GrowthRates,
1 + GrowthRates[GrowthRate]
)For growth rates of 10% and 5%, the combined growth factor is 1.10 × 1.05 = 1.155. This represents a cumulative increase of 15.5%, assuming the rates apply sequentially to the same starting amount. Growth-rate data should be stored as decimals, such as 0.10 for 10%.
RANKX is an iterator-style ranking function. It evaluates an expression over a table and ranks the current value among the evaluated values. It is commonly used to rank products, salespeople, regions, or customers by a measure.
RANKX(
<table>,
<expression>,
[<value>],
[<order>],
[<ties>]
)Product Revenue Rank =
RANKX(
ALL(Products[ProductName]),
[Total Revenue],
,
DESC,
DENSE
)This measure ranks products by total revenue from highest to lowest. ALL(Products[ProductName]) removes the filter on product name from the ranking set, so products can be compared with one another. Other filters, such as date or region, can still affect the revenue measure.
The DENSE option assigns consecutive ranks when values are tied. For example, values ranked 1, 2, 2, 3 receive ranks 1, 2, 2, 3. With SKIP, the same values receive ranks 1, 2, 2, 4.
CONCATENATEX evaluates an expression for each row of a table, converts the results to text as needed, and concatenates them into a single string. It is useful for displaying lists of products, categories, or selected items in a card or tooltip.
CONCATENATEX(
<table>,
<expression>,
[<delimiter>],
[<orderBy_expression>],
[<order>]
)Selected Product Names =
CONCATENATEX(
VALUES(Products[ProductName]),
Products[ProductName],
", ",
Products[ProductName],
ASC
)This measure returns the distinct product names visible in the current context, separated by commas and sorted alphabetically. If many products are visible, the resulting text may be long, so use it carefully in report visuals.
The table argument is one of the most important parts of an iterator formula. It determines the rows over which the expression is evaluated, and therefore what the calculation means.
Total Revenue =
SUMX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)This evaluates revenue for every visible sales row. It is appropriate when the desired calculation is based on individual sales lines.
Average Revenue per Product =
AVERAGEX(
VALUES(Products[ProductID]),
[Total Revenue]
)This evaluates total revenue once for each distinct product ID in the current context. Products with no qualifying sales may return blank for the measure and are handled according to the iterator's blank-value behavior.
Average Daily Revenue =
AVERAGEX(
VALUES('Date'[Date]),
[Total Revenue]
)This measure averages total revenue across the distinct dates present in the current date context. The result can include dates with no sales if those dates are present in the visible date table and the measure returns zero for them. If the measure returns blank, blank results are generally excluded from the average.
To make the intended meaning explicit, decide whether the average should include every calendar date, only dates with transactions, or only dates with nonblank revenue. These definitions can produce different results.
Iterator expressions can reference existing measures. This is often easier to maintain than repeating a complex formula. A measure is evaluated under the context established for the current row and any applicable filters.
Average Monthly Revenue =
AVERAGEX(
VALUES('Date'[YearMonth]),
[Total Revenue]
)This measure evaluates total revenue for each distinct year-month value in the current context, then averages the monthly results. Ensure that 'Date'[YearMonth] uniquely identifies each month across years, for example, using a value such as 2026-01 rather than the month name alone.
Total Revenue across Visible Customers =
SUMX(
VALUES(Customers[CustomerID]),
[Total Revenue]
)This evaluates total revenue for each distinct visible customer ID and sums the results. Depending on the data model and the intended business definition, this can be useful for customer-level analysis. If customer groups overlap or the measure's logic is nonadditive, summing entity-level results may not match a direct total calculation.
CALCULATE evaluates an expression in a modified filter context. When a measure or CALCULATE expression is evaluated inside an iterator, row context can be converted into filter context through context transition.
Revenue for Visible Customers =
SUMX(
VALUES(Customers[CustomerID]),
CALCULATE([Total Revenue])
)Because [Total Revenue] is a measure, its evaluation inside the iterator automatically receives context transition. Writing CALCULATE explicitly here can make the intent clearer, although it is usually not necessary merely to trigger context transition for a measure reference.
Context transition is especially important when an expression must evaluate an aggregation for the current iterator row. Without the appropriate context transition, a calculation may repeat a broader total instead of calculating the result for each individual entity.
For reliable DAX, check which table is being iterated, which columns identify the current row, how relationships propagate filters, and whether the expression is a measure or a raw aggregation.
Iterator functions are often combined with table functions to control which rows or entities are evaluated.
FILTER returns a table containing only rows that satisfy a condition. The returned table can be supplied to an iterator.
Revenue from Products Above 500 =
SUMX(
FILTER(
Sales,
Sales[UnitPrice] > 500
),
Sales[Quantity] * Sales[UnitPrice]
)This formula includes sales rows whose unit price is greater than 500 and then sums the calculated revenue for those rows.
VALUES returns the distinct values of a column or the rows of a table visible in the current context, with behavior that can include a blank value in certain circumstances.
Average Revenue per Region =
AVERAGEX(
VALUES(Geography[Region]),
[Total Revenue]
)This measure evaluates revenue for each distinct visible region and averages the results. It can be used to compare the average revenue across regions instead of averaging individual sales rows.
SUMMARIZE can produce a grouped table that is then passed to an iterator. For many modern DAX patterns, SUMMARIZE is useful for grouping, while ADDCOLUMNS can be used to add expressions to a table. Choose the pattern that best expresses the intended grouping and calculation.
Average Revenue per Category =
AVERAGEX(
VALUES(Products[Category]),
[Total Revenue]
)For a straightforward distinct-category average, VALUES is sufficient. Use a more complex grouped table only when the calculation genuinely requires additional grouping columns or a different table shape.
Imagine a retail company with a Power BI model containing these tables:
Sales: Order ID, Product ID, Quantity, Unit Price, Unit Cost, Discount Rate, and Order Date.Products: Product ID, Product Name, Category, and Brand.Customers: Customer ID, Customer Name, and Segment.Date: Date, Month, Quarter, and Year.Assume the relationships between these tables are correctly configured. The following measures can support a sales dashboard.
Total Revenue =
SUMX(
Sales,
Sales[Quantity] *
Sales[UnitPrice] *
(1 - Sales[DiscountRate])
)Total Cost =
SUMX(
Sales,
Sales[Quantity] * Sales[UnitCost]
)Total Profit =
[Total Revenue] - [Total Cost]This measure subtracts total cost from discounted revenue. It assumes the cost is not reduced by the sales discount and that the business definition of profit is revenue minus the stated product cost. Adjust the formula if returns, shipping, taxes, or other expenses must be included.
Average Sales-Line Revenue =
AVERAGEX(
Sales,
Sales[Quantity] *
Sales[UnitPrice] *
(1 - Sales[DiscountRate])
)Highest Sales-Line Revenue =
MAXX(
Sales,
Sales[Quantity] *
Sales[UnitPrice] *
(1 - Sales[DiscountRate])
)Product Revenue Rank =
RANKX(
ALL(Products[ProductName]),
[Total Revenue],
,
DESC,
DENSE
)These measures can be used in cards, matrices, tables, bar charts, and line charts. Slicers for date, category, region, and customer segment can change the filter context and therefore the results.
Problem: A measure needs to multiply quantity by price, but the formula attempts to sum a product of columns directly.
Solution: Use SUMX to evaluate the multiplication row by row.
Total Revenue =
SUMX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)Problem: An average intended to represent revenue per product is calculated over every row in Sales.
Solution: Iterate over distinct products and evaluate the revenue measure for each product.
Average Revenue per Product =
AVERAGEX(
VALUES(Products[ProductID]),
[Total Revenue]
)Problem: A measure returns the same total for each row or entity when different values are expected.
Solution: Check whether the expression is evaluated in the appropriate context. Measures inside iterators generally receive context transition automatically, while raw aggregations may need CALCULATE when row context must become filter context.
Problem: The average does not match a spreadsheet calculation.
Possible causes:
Solution: Write down what one observation represents, construct the iterator table accordingly, and test the measure in a table visual alongside the entity being averaged.
Problem: Products all receive the same rank or ranking changes unexpectedly.
Possible causes:
Solution: Place product name and total revenue in a table visual first. Verify that revenue differs appropriately by product, then add the ranking measure and confirm the intended filter behavior.
Problem: A report becomes slow after several iterator measures are added.
Possible causes:
Solution:
Create a measure that multiplies quantity by unit price for every sales row and adds the results.
Gross Revenue =
SUMX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)Create a measure that averages quantity multiplied by unit price across sales rows.
Average Sales-Line Revenue =
AVERAGEX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)Create a measure that returns the largest calculated sales-line revenue.
Highest Line Value =
MAXX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)Create a measure that calculates revenue for each distinct customer and averages the customer-level results.
Average Revenue per Customer =
AVERAGEX(
VALUES(Customers[CustomerID]),
[Total Revenue]
)Create a measure that ranks products by total revenue, with the highest revenue receiving rank 1.
Product Rank =
RANKX(
ALL(Products[ProductName]),
[Total Revenue],
,
DESC,
DENSE
)Create a measure that averages revenue across the distinct dates in the current date context.
Average Daily Revenue =
AVERAGEX(
VALUES('Date'[Date]),
[Total Revenue]
)Build a Power BI report using iterator functions to analyze revenue, profitability, average transaction values, and product performance.
SUMX.AVERAGEX.MINX and MAXX.RANKX.CONCATENATEX.After completing this project, you should understand how iterator functions evaluate expressions row by row, how the selected iterator table changes the meaning of a calculation, and how DAX measures respond to filter context in interactive Power BI reports.
Answer: An iterator evaluates an expression for each row of a table and then processes the resulting values. Depending on the function, it can sum, average, count, find a minimum or maximum, rank values, or concatenate text.
Answer: SUM adds the values of a single column. SUMX evaluates an expression for each row in a table and sums the resulting values.
Answer: AVERAGE calculates the mean of a numeric column. AVERAGEX calculates the mean of the numeric results produced by an expression evaluated for each row of a table.
Answer: Use SUMX when the row-level calculation needs to be evaluated dynamically within a measure. A calculated column can be useful for a stable row-level value that needs to be stored and reused, but it increases model size and is computed during data refresh or model recalculation rather than dynamically for each report filter context.
Answer: Row context is the context in which DAX evaluates an expression for a particular row. Iterator functions create row context as they process each row in the supplied table.
Answer: Filter context is the set of filters affecting a calculation. It can come from slicers, visual groupings, page and report filters, relationships, and DAX expressions.
Answer: MINX evaluates an expression for each row and returns the smallest numeric result.
Answer: MAXX evaluates an expression for each row and returns the largest numeric result.
Answer: RANKX ranks entities such as products, customers, or salespeople based on a measure or expression, in ascending or descending order.
Answer: VALUES can provide a distinct set of values for the iterator to process. For example, AVERAGEX(VALUES(Products[ProductID]), [Total Revenue]) averages revenue across the visible distinct products.
Answer: Iterators can be expensive when they process large tables or repeat complex calculations. Performance can often be improved by selecting the appropriate table grain, simplifying expressions, avoiding unnecessary nested iterators, and measuring query performance.
Answer: The iterator might process a different set of rows, exclude blank expression results, or use a different level of granularity. The current report filter context may also change which rows are evaluated.
SUMX evaluates an expression per row and sums the results.AVERAGEX evaluates an expression per row and averages the numeric results.MINX returns the smallest numeric expression result.MAXX returns the largest numeric expression result.COUNTX and COUNTAX count eligible nonblank expression results according to their supported data types.PRODUCTX multiplies the numeric expression results across rows.RANKX ranks values across a supplied table.CONCATENATEX joins expression results into a text string.Iterator functions are essential for writing flexible DAX calculations in Power BI. SUMX and AVERAGEX are especially useful when a calculation must be performed for each row before the results are aggregated. Other iterator functions, including MINX, MAXX, COUNTX, COUNTAX, PRODUCTX, RANKX, and CONCATENATEX, support a wide range of numerical, ranking, counting, and text-analysis tasks.
The most important skills are selecting the correct table to iterate over, understanding row context and filter context, defining the correct business-level grain, and validating results under different report filters. With these concepts, you can create more accurate, maintainable, and useful Power BI reports.
